﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MISAPP.Common;
using System.Data;
using System.Transactions;

namespace MISAPP.Classify.DataAccess
{
    public class DA_CFG004:DA_Matrix
    {
        protected bool setTableSchema = false;
        DataTable cardRange = null;

        public DA_CFG004()
        {
            tableName = "CFG004";
        }

        public bool Insert(string p_category,  object[,] p_card
            , string p_ym, object[,] p_amount)
        {
            string sql = @"
INSERT INTO CFG004(category,card,ym,AMOUNT) 
VALUES(@category,@card,@ym,@AMOUNT)
";
            if (setTableSchema == false)
            {
                setTableSchema = true;
                FillTableSchema(tableName);
            }
            int iResult = -1;

            using (TransactionScope scop = new TransactionScope())
            {
                try
                {
                    for (int i = 1; i <= p_amount.GetLength(1); i++)
                    {
                        if (p_card[1, i] != null)
                        {
                            ResetCriteria();
                            FillFieldList(new string[] { p_category }
                                    , new string[] { "@category" }, "category");
                            FillFieldList(new string[] { p_card[1, i].ToString() }
                                    , new string[] { "@card" }, "card");
                            FillFieldList(new string[] { p_ym }
                                    , new string[] { "@ym" }, "ym");
                            FillFieldList(new string[] { p_amount[1, i].ToString() }
                                    , new string[] { "@AMOUNT" }, "AMOUNT");

                            AddParameter(sql);
                            iResult = Common.DAL.ExecuteNoneQuery(cmd);
                            if (iResult < 0)
                            {
                                throw new Exception("Insert Fail");
                            }
                        }

                    }
                    scop.Complete();
                }
                catch (Exception ex)
                {
                    if (iResult > -1)
                    {
                        scop.Complete();
                    }
                    else
                    {
                        iResult = -1;
                    }
                    
                }
            }
            return iResult < 0 ? false : true;
        }

        public DataTable GetSummaryData(string p_ym)
        {
            string sql = string.Format(getSqlStatement()
                , p_ym
                , getField(p_ym));

            return getData(sql,"amount");
        }

        public void Check(string p_ym)
        {
            string sql = "DELETE FROM CFG004 WHERE YM = @p_ym";
            System.Data.Common.DbCommand cmd = DAL.GetCommand();

            cmd.Parameters.Add(DAL.GetParameter("@p_ym", System.Data.DbType.String
                , p_ym, cmd.CreateParameter()));
            cmd.CommandText = sql;
            DAL.ExecuteNoneQuery(cmd);
        }

        /// <summary>
        /// 找出每個類別它的範圍
        /// </summary>
        /// <returns></returns>
        public string GetCardRange(string p_category)
        {
            if (cardRange == null)
            { 
                cardRange = Utility.GetParameterByLevel("CFG", "AMOUNT");
            }

             IQueryable<DataRow> query = (from rng in cardRange.AsEnumerable()
                                         where rng.Field<string>("fldname")
                                         .ToString().Equals(p_category)
                                          select rng   ).AsQueryable();
            string range = string.Empty;
            foreach(DataRow row in query)
            {
                range = row["fldValue"].ToString();
            }
            return range;
        }

        protected string getField(string p_yyy)
        {
            string fld,str;
            str = @"
,MAX(CASE WHEN A.ym='{0}' THEN ROUND(A.AMOUNT/1000000,2) ELSE 0 END) [{0}]";
            fld = string.Format(
"{0}01,{0}02,{0}03,{0}04,{0}05,{0}06,{0}07,{0}08,{0}09,{0}10,{0}11,{0}12",p_yyy);
            return Utility.GetYearMonthString(fld.Split(new char[] { ',' })
                , str);
        }

        protected string getSqlStatement()
        {
            /*
             * ,MAX(CASE WHEN A.ym='10002' THEN CONVERT(NUMERIC,A.AMOUNT) ELSE 0 END) [10002]
             */
            return @"
SELECT
	A.card AS [卡別/簽帳金額]
	{1}
FROM(
    SELECT 
		card
		,ROW_NUMBER() OVER(ORDER BY AMOUNT DESC) SEQ
	FROM CFG004
	WHERE card NOT LIKE '%合計%'
	AND ym = (SELECT TOP 1 ym
				FROM CFG004
				ORDER BY YM DESC)
	)B LEFT OUTER JOIN CFG004 A
ON B.card = A.card
WHERE A.ym BETWEEN '{0}01' AND '{0}12'
GROUP BY A.card ,B.SEQ
ORDER BY B.SEQ


";
        }
    }
}
